#include <sstream>
#include <iomanip>
#include <openssl/md5.h>
#include <PopupLoggerUI.hpp>
#include <PopupLibTypes.hpp>
#include <PopupSqlite3Database.hpp>
#include <PopupOSAL.hpp>
#include "CppSQLite3.h"

using namespace std;
using namespace Popup;
using namespace PopupUtils;

const char *databaseCreateRequest =
    "CREATE TABLE users("
    " id       INTEGER PRIMARY KEY,"
    " login    VARCHAR(32) UNIQUE NOT NULL,"
    " hash     VARCHAR(32),"
    " nickname TEXT,"
    " avatar   TEXT);"
    ""
    "CREATE TABLE stats("
    " userid        INTEGER,"
    " itemkey       INTEGER default 0,"
    " itemcount     INTEGER default 0,"
    " FOREIGN KEY(userid) REFERENCES users(id));"
    ""
    "CREATE TABLE records("
    " itemkey     INTEGER PRIMARY KEY,"
    " recordman   INTEGER,"
    " record      INTEGER default 0,"
    " FOREIGN KEY(recordman) REFERENCES users(id),"
    " FOREIGN KEY(itemkey) REFERENCES stats(itemkey));";

//==============================================================================
// Static function(s) declaration(s)
//==============================================================================

static string computeHash(const string & p_login,
                          const string & p_password);

//==============================================================================
// Constructor/Destructor
//==============================================================================

PopupSqlite3Database::PopupSqlite3Database(const string & p_path)
: db(new CppSQLite3DB), connected(false)
{
  bool success = true;
  bool doInit = false;

  // Create the path where database should be stored in case the
  // database file doesn't exists.
  if (!PopupUtils::ftest(p_path, PopupUtils::REGULAR_FILE)) {
    doInit = true;
    info("Database file doesn't exists."
                      " It will be created");
    if (!PopupUtils::mkdir(PopupUtils::dirname(p_path),0755)) {
      error("Cannot create folder where database"
                        " should be stored. Aborting.");
      success = false;
    }
  }
  // Open database
  if (success) {
    try {
      db->open(p_path.c_str());
      connected = true;
    }
    catch (CppSQLite3Exception & e) {
      error("Failed to open SQL file %s : %s",
                        p_path.c_str(), e.errorMessage());
      success = false;
    }
  }

  // Init database in case file would just have been created
  if (success && doInit) {
    info("Initializing database");
    db->execDML(databaseCreateRequest);
  }
}
//----------------------------------------------------------

PopupSqlite3Database::~PopupSqlite3Database()
{
  // Close database
  if (connected == true) {
    db->close();
  }
}

//==============================================================================
// Database access
//==============================================================================

bool PopupSqlite3Database::getAllUsers(UserLoginMap & p_usersMap)
{
  bool _rc = false;

  CppSQLite3Statement _statement = db->compileStatement("SELECT * FROM users");
  CppSQLite3Query _query = _statement.execQuery();

  if (!_query.eof())
  {
    int idCol     = _query.fieldIndex("id");
    int loginCol  = _query.fieldIndex("login");
    int nnameCol  = _query.fieldIndex("nickname");
    int avatarCol = _query.fieldIndex("avatar");

    while (!_query.eof())
    {
      User *_user = new User();
      _user->id       = _query.getIntField(idCol);
      _user->login    = _query.getStringField(loginCol);
      _user->nickname = _query.getStringField(nnameCol);
      _user->avatar   = _query.getStringField(avatarCol);
      p_usersMap[_user->login] = _user;
      _query.nextRow();
    }

    _rc = true;
  }

  return _rc;
}

bool PopupSqlite3Database::authenticate(const string & p_login,
                                        const string & p_password,
                                        string & p_message)
{
  bool _rc = false;

  CppSQLite3Statement _statement = 
      db->compileStatement("SELECT hash FROM users WHERE login=?");
  _statement.bind(1, p_login.c_str());
  CppSQLite3Query _query = _statement.execQuery();

  if (_query.eof())
  {
    p_message = "Fuck you bastard! You are not registered in database!";
    error(p_message.c_str());
  }
  else
  {
    // Compute user hash
    string hash = computeHash(p_login, p_password);
    // Get stored hash
    int hashCol = _query.fieldIndex("hash");
    string refHash = _query.getStringField(hashCol);
    // Make sure hash values match
    if (refHash.compare(hash) != 0)
    {
      p_message = "Oops! Wrong password!";
      error(p_message.c_str());
    }
    else
    {
      // Validated!
      _rc = true;
    }
  }

  return _rc;
}
//----------------------------------------------------------

ConnectionEcode PopupSqlite3Database::newUser(const string & p_login,
                                              const string & p_password,
                                              const string & p_nickname,
                                              const string & p_avatar,
                                              User **p_user,
                                              string & p_message)
{
  // Make sure login is not empty
  if (p_login.length() <= 0) {
    p_message = "Login is empty";
    error(p_message.c_str());
    return POPUP_CONNECTION_ERR_EMPTY_LOGIN;
  }

  // Make sure
  else if (existsUser(p_login))
  {
    p_message = "Another user already has the same login!";
    error(p_message.c_str());
    return POPUP_CONNECTION_ERR_LOGIN_ALREADY_EXISTS;
  }

  // Seems OK!
  else
  {
    // Compute user hash
    string _hash = computeHash(p_login, p_password);

    try {
      // Setup query statement
      CppSQLite3Statement _statement = 
          db->compileStatement("INSERT INTO "
              "users(login,nickname,hash,avatar) "
              "VALUES(?, ?, ?, ?)");
      _statement.bind(1, p_login.c_str());
      _statement.bind(2, p_nickname.c_str());
      _statement.bind(3, _hash.c_str());
      _statement.bind(4, p_avatar.c_str());

      // Execute query
      CppSQLite3Query _query = _statement.execQuery();

      // Retrieve new user ID
      CppSQLite3Statement _statement2 =
          db->compileStatement("SELECT id FROM users where login=?");
      _statement2.bind(1, p_login.c_str());
      CppSQLite3Query _query2 = _statement2.execQuery();
      if (!_query2.eof())
      {
        User *_user = new User();
        _user->id       = _query2.getIntField(0);
        _user->login    = p_login;
        _user->nickname = p_nickname;
        _user->avatar   = p_avatar;
        *p_user = _user;
        return POPUP_CONNECTION_SUCCESS;
      }
      else
      {
        return POPUP_CONNECTION_ERR_UNKNOWN;
      }
    }
    catch (CppSQLite3Exception & e) {
      p_message = "Database error! Failed to create new user!";
      p_message += e.errorMessage();
      error(
                        p_message.c_str());
      return POPUP_CONNECTION_ERR_SQLITE;
    }
  }
}
//----------------------------------------------------------

bool PopupSqlite3Database::existsUser(const string & p_login)
{
  CppSQLite3Statement _statement = 
      db->compileStatement("SELECT * FROM users WHERE login=?");
  _statement.bind(1, p_login.c_str());
  CppSQLite3Query _query = _statement.execQuery();
  return (!_query.eof());
}
//----------------------------------------------------------

bool PopupSqlite3Database::updateNickname(User *p_user,
                                          const string & p_newNickname,
                                          string & p_message)
{
  bool _rc = false;
  try {
    // Setup query statement
    CppSQLite3Statement _statement = 
        db->compileStatement("UPDATE users SET nickname=? WHERE (id=?)");
    _statement.bind(1, p_newNickname.c_str());
    _statement.bind(2, (int) p_user->getID());

    // Execute query
    CppSQLite3Query _query = _statement.execQuery();
    p_user->nickname = p_newNickname;
    _rc = true;
  }
  catch (CppSQLite3Exception & e) {
    p_message = "Failed to update user nickname : ";
    p_message += e.errorMessage();
    error(
                      p_message.c_str());
  }
  return _rc;
}
//----------------------------------------------------------

bool PopupSqlite3Database::updateAvatar(User *p_user,
                                        const string & p_newAvatar,
                                        string & p_message)
{
  bool _rc = false;
  try {
    // Setup query statement
    CppSQLite3Statement _statement = 
        db->compileStatement("UPDATE users SET avatar=? WHERE (id=?)");
    _statement.bind(1, p_newAvatar.c_str());
    _statement.bind(2, (int) p_user->getID());

    // Execute query
    CppSQLite3Query _query = _statement.execQuery();
    p_user->avatar = p_newAvatar;
    _rc = true;
  }
  catch (CppSQLite3Exception & e) {
    p_message = "Failed to update user avatar : ";
    p_message += e.errorMessage();
    error(
                      p_message.c_str());
  }
  return _rc;
}
//----------------------------------------------------------

bool PopupSqlite3Database::updateUserRates(UserID p_userID,
                                           UserRateItem p_itemKey,
                                           // ItemUpdateOperation p_itemUpdateOperation,
                                           StatisticItemUpdateResult & p_updateResult,
                                           string & p_message)
{
  bool _rc = true;
  CppSQLite3Statement _statement;

  try {
    // Create item if not already in the table
    _statement = db->compileStatement("SELECT itemcount FROM stats WHERE userid=? AND itemkey=?");
    _statement.bind(1, (int) p_userID);
    _statement.bind(2, p_itemKey);
    if (_statement.execQuery().eof()) {
      // if (p_itemUpdateOperation == ITEM_UPDATE_INCREMENT) {
        _statement = db->compileStatement("INSERT INTO "
            "stats(userid,itemkey,itemcount) "
            "VALUES(?, ?, 1)");
      /*} else {
        _statement = db->compileStatement("INSERT INTO "
            "stats(userid,itemkey,itemcount) "
            "VALUES(?, ?, 0)");
      }*/
    }
    // Or udpate it!
    else {
      //if (p_itemUpdateOperation == ITEM_UPDATE_INCREMENT) {
        _statement =
            db->compileStatement("UPDATE stats "
                "SET itemcount=itemcount+1 "
                "WHERE userid=? and itemkey=?");
      /*} else {
        _statement =
            db->compileStatement("UPDATE stats "
                "SET itemcount=itemcount-1 "
                "WHERE userid=? and itemkey=?");
      }*/
    }

    _statement.bind(1, (int) p_userID);
    _statement.bind(2, p_itemKey);
    _statement.execQuery();

    // Get new value for output parameter and information
    // from the records table
    _statement = db->compileStatement(
        "SELECT stats.itemkey, stats.itemcount, record, recordman FROM stats "
        "LEFT JOIN records ON stats.itemkey=records.itemkey "
        "WHERE userid=? AND stats.itemkey=?");
    _statement.bind(1, (int) p_userID);
    _statement.bind(2, p_itemKey);

    CppSQLite3Query _query = _statement.execQuery();
    if (_query.eof()) {
      p_message = "Ooops! Cannot retrieve updated value!";
      _rc = false;
    }
    else
    {
      // Set new item value
      p_updateResult.newItemValue = (unsigned long)
          _query.getIntField(_query.fieldIndex("itemcount"));

      // Get current record if any
      int _recordFieldIndex    = _query.fieldIndex("record");
      int _recordmanFieldIndex = _query.fieldIndex("recordman");

      // No record found... => New recooooooord!
      if (_query.fieldIsNull(_recordFieldIndex)) {

        p_updateResult.isNewRecord = true;

        // Register new record in database
        _statement = db->compileStatement(
            "INSERT INTO records(itemkey,record,recordman) VALUES(?,?,?)");
        _statement.bind(1, p_itemKey);
        _statement.bind(2, (int) p_updateResult.newItemValue);
        _statement.bind(3, (int) p_userID);
        _statement.execQuery();

      } else {
          unsigned long _previousRecord =
              _query.getIntField(_recordFieldIndex);

          // Previous record beaten!
          if (p_updateResult.newItemValue > _previousRecord) {

            p_updateResult.isNewRecord = true;
            p_updateResult.previousRecordValue = _previousRecord;
            p_updateResult.previousRecordman =
                (UserID) _query.getIntField(_recordmanFieldIndex);

            // Register new record in database
            _statement = db->compileStatement(
                "UPDATE records SET record=?, recordman=? WHERE itemkey=?");
            _statement.bind(1, (int) p_updateResult.newItemValue);
            _statement.bind(2, (int) p_userID);
            _statement.bind(3, p_itemKey);
            _statement.execQuery();
          }
      }
    }
  }
  catch (CppSQLite3Exception & e) {
    p_message = "Failed to update statistics: ";
    p_message += e.errorMessage();
    error(
                      p_message.c_str());
    _rc = false;
  }

  return _rc;
}


bool PopupSqlite3Database::getUserStatitics(UserID p_userID,
                      UserStatistics & p_stats,
                      std::string & p_message)
{
  bool _rc = false;
  CppSQLite3Statement _statement;

  p_stats.clear();
  p_stats.recordMask = 0;

  try {
    _statement = db->compileStatement(
        "SELECT stats.itemkey, stats.itemcount, records.recordman FROM stats "
        "LEFT JOIN records ON records.recordman=stats.userid AND "
        "                     records.itemkey=stats.itemkey "
        "WHERE userid=?;");
    _statement.bind(1, (int) p_userID);

    CppSQLite3Query _query = _statement.execQuery();

    if (!_query.eof()) {
      int itemKeyIndex    = _query.fieldIndex("itemkey");
      int itemCountIndex  = _query.fieldIndex("itemcount");
      int itemRecordIndex = _query.fieldIndex("recordman");

      while (!_query.eof()) {
    	// Get item we're about to setup
        UserRateItem _item    = (UserRateItem) _query.getIntField(itemKeyIndex);
    	// Fill statistics structure
        p_stats[_item] = (unsigned long) _query.getIntField(itemCountIndex);
        if (!_query.fieldIsNull(itemRecordIndex)) {
        	p_stats.recordMask |= _item;
        }
        _query.nextRow();
      }
    }
    _rc = (p_stats.size() > 0);
  }
  catch (CppSQLite3Exception & e) {
    p_message = "Failed to get statistics: ";
    p_message += e.errorMessage();
    error(
                      p_message.c_str());
  }

  return _rc;
}

//==============================================================================
// Utilities
//==============================================================================

static string computeHash(const string & p_login,
                          const string & p_password)
{
  const char *_hashSalt = "zo5pro$1pvkhj6*cz4a8ùtvb#ui4oeuio";
  MD5_CTX _md5Ctxt;
  unsigned char _md5Sum[MD5_DIGEST_LENGTH];
  stringstream s;

  MD5_Init(&_md5Ctxt);
  MD5_Update(&_md5Ctxt, p_login.c_str(), p_login.length());
  MD5_Update(&_md5Ctxt, _hashSalt, strlen(_hashSalt));
  MD5_Update(&_md5Ctxt, p_password.c_str(), p_password.length());
  MD5_Final(&_md5Sum[0], &_md5Ctxt);

  for (size_t i = 0; i < MD5_DIGEST_LENGTH; i+=2) {
    unsigned short *_md5 = (unsigned short*) &_md5Sum[i];
    s << hex << setw(4) << setfill('0') << *_md5;
  }
  return s.str();
}


